{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 用 Python 选择单元格"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 选择单个单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws['A1'] #ws 为某一个 Sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws.cell(row=1, column=1) #ws 为某一个 Sheet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 选择多个单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws[10] #ws 为某一个 Sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws['C'] #ws 为某一个 Sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws[5:10] #ws 为某一个 Sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws['C:E'] #ws 为某一个 Sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws[\"A1\":\"C5\"] #ws 为某一个 Sheet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 用Python设置Excel字体"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "aRGB颜色值在线转换：http://www.yuangongju.com/color"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  基本字体相关设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#对每一个单元格设置\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import Font\n",
    "from openpyxl.styles import colors\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "rows = [[\"字体\"],\n",
    "        [\"字体大小\"],\n",
    "        [\"是否加粗\"],\n",
    "        [\"是否斜体\"],\n",
    "        [\"垂直对齐\"],\n",
    "        [\"下划线\"],\n",
    "        [\"删除线\"],\n",
    "        [\"字体颜色\"]]\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "    \n",
    "a1 = ws['A1']\n",
    "a1.font = Font(name = \"arial\")\n",
    "a2 = ws[\"A2\"]\n",
    "a2.font = Font(size = 16)\n",
    "a3 = ws[\"A3\"]\n",
    "a3.font = Font(bold = True)\n",
    "a4 = ws[\"A4\"]\n",
    "a4.font = Font(italic = True)\n",
    "a5 = ws[\"A5\"]\n",
    "a5.font = Font(vertAlign = \"superscript\")\n",
    "a6 = ws[\"A6\"]\n",
    "a6.font = Font(underline = \"doubleAccounting\")\n",
    "a7 = ws[\"A7\"]\n",
    "a7.font = Font(strike = True)\n",
    "a8 = ws[\"A8\"]\n",
    "a8.font = Font(color = \"FFEE0000\")\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\font.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 单元格填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import colors\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "ws['A1'] = \"我是纯色填充\"\n",
    "a1 = ws['A1']\n",
    "a1.fill = PatternFill(fill_type = \"solid\",start_color = 'FFFFFF00')\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\fill.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 边框线设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import colors\n",
    "from openpyxl.styles import Border, Side\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "ws['B2'] = \"边框\"\n",
    "a1 = ws['B2']\n",
    "a1.border = Border(left = Side(border_style = \"hair\",color = \"FFEE0000\"),\n",
    "                   right = Side(border_style = \"thick\",color = \"CC0D86F3\"),\n",
    "                   top = Side(border_style = \"thick\",color = \"FF00A8FF\"),\n",
    "                   bottom = Side(border_style = \"thick\",color = \"FF11FF00\"),)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\border.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import colors\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "ws['B2'] = \"边框B2\"\n",
    "a1 = ws['B2']\n",
    "a1.border = Border(diagonal = Side(border_style = \"thick\",color = \"FFEE0000\"),\n",
    "                   diagonalDown=True)\n",
    "\n",
    "ws['C3'] = \"边框C3\"\n",
    "a2 = ws['C3']\n",
    "a2.border = Border(diagonal = Side(border_style = \"thick\",color = \"FFEE0000\"),\n",
    "                   diagonalUp=True)\n",
    "\n",
    "ws['D4'] = \"边框D4\"\n",
    "a2 = ws['D4']\n",
    "a2.border = Border(diagonal = Side(border_style = \"thick\",color = \"FFEE0000\"),\n",
    "                   diagonalDown=True,diagonalUp=True)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\border1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 案例：批量设置单元格字体"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#对全部数据进行设置(通过列进行设置)\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import colors\n",
    "from openpyxl.styles import Font\n",
    "from openpyxl.styles import PatternFill\n",
    "from openpyxl.styles import Border, Side\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['col_1', 'col_2', 'col_3'],\n",
    "    [2, 30, 30],\n",
    "    [3, 30, 25],\n",
    "    [4, 40, 30],\n",
    "    [5, 20, 10],\n",
    "    [6, 25, 5],\n",
    "    [7, 50, 10],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "for col in ws[\"A\":\"C\"]:\n",
    "    for c in col:\n",
    "        c.font = Font(name = '微软雅黑',size = 12,color = \"FFFFFFFF\")\n",
    "        c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')\n",
    "        c.border = Border(left = Side(border_style = \"thin\",color = \"FF0F0F0F\"),\n",
    "                   right = Side(border_style = \"thin\",color = \"FF0F0F0F\"),\n",
    "                   top = Side(border_style = \"thin\",color = \"FF0F0F0F\"),\n",
    "                   bottom = Side(border_style = \"thin\",color = \"FF0F0F0F\"))\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\font_all_style.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#遍历每一行\n",
    "for row in ws[1:7]:\n",
    "    #遍历每一行中的每一列\n",
    "    for c in row:\n",
    "        c.font = Font()\n",
    "        c.fill = PatternFill()\n",
    "        c.border = Border()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#遍历指定区域中的每一行\n",
    "for row in ws[\"B3\":\"C5\"]:\n",
    "    #遍历每一行中的每一列\n",
    "    for c in row:\n",
    "        c.font = Font()\n",
    "        c.fill = PatternFill()\n",
    "        c.border = Border()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "789px",
    "left": "69px",
    "top": "110px",
    "width": "308.514px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
